<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_calon_siswa extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }

	// fungsi dll
	function get_ai(){
		$q = "SELECT AUTO_INCREMENT AS ai FROM  INFORMATION_SCHEMA.TABLES ";
		$q .= "WHERE TABLE_SCHEMA = '".$this->db->database."' AND TABLE_NAME = 'tbl_sd_calonsiswa';";
		$inc = $this->db->query($q);
		
		return $inc->row()->ai;
	}
	
	function get_tahunmasuk(){
		$this->db->flush_cache();
		$this->db->where('status', '1');
		$res = $this->db->get('tbl_sd_psb');
		
		if($res->num_rows() > 0)
			return $res->row()->tahun;
		else
			return date('Y');
	}
	
	// untuk grid
	function get_data($plimit=true){
		# get parameter from easy grid
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'no_pendaftaran';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		$offset = ($page-1)*$limit;
		
		#get filter
		$id_psb = isset($_POST['id_psb']) ? strval($_POST['id_psb']) : '';
		$id_kelompok = isset($_POST['id_kelompok']) ? strval($_POST['id_kelompok']) : '';
		$chk_1 = isset($_POST['chk_1']) ? filter_var(strval($_POST['chk_1']), FILTER_VALIDATE_BOOLEAN) : true;
		$chk_2 = isset($_POST['chk_2']) ? filter_var(strval($_POST['chk_2']), FILTER_VALIDATE_BOOLEAN) : true;
		$chk_3 = isset($_POST['chk_3']) ? filter_var(strval($_POST['chk_3']), FILTER_VALIDATE_BOOLEAN) : true;
		$chk_4 = isset($_POST['chk_4']) ? filter_var(strval($_POST['chk_4']), FILTER_VALIDATE_BOOLEAN) : true;
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.*, b.nama_psb, c.nama_kelompok');
			$this->db->from('tbl_sd_calonsiswa a');
			$this->db->join('tbl_sd_psb b', 'b.id_psb=a.id_psb');
			$this->db->join('tbl_sd_psb_kelompok c', 'c.id_kelompok=a.id_kelompok');
			
			$this->db->where('a.id_psb', $id_psb);
			$this->db->where('a.id_kelompok', $id_kelompok);

			$stat[] = 0;
			 
			if($chk_1)
				$stat[] = 1;
			if($chk_2)
				$stat[] = 2;
			if($chk_3)
				$stat[] = 3;
			if($chk_4)
				$stat[] = 4;
			
			$this->db->where_in('a.status', $stat);
			
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		if($plimit == true){
			if($page != 0)
				$this->db->limit($limit, $offset);
		}
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function get_data_edit($id){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('tbl_sd_calonsiswa');
		$this->db->where('id', $id);
		
		return $this->db->get();
	}
	
	function togrid($data, $count){
		$response->total = $count;
		$response->rows = array();
		if($count>0){
			$i=0;
			foreach($data->result_array() as $row){
				foreach($row as $key => $value){
					$response->rows[$i][$key]=$value;
				}
				$i++;
			}
		}
		
		return json_encode($response);
	}
	
	
	// untuk crud
	function no_pendaftaran_exist($no_pendaftaran, $id_psb){
		$this->db->flush_cache();
		$this->db->where('no_pendaftaran', $no_pendaftaran);
		$this->db->where('id_psb', $id_psb);
		$res = $this->db->get('tbl_sd_calonsiswa');
		
		return ($res->num_rows() > 0);
	}
	
	function insert($data){
		// get last auto increment
		$inc = $this->get_ai();
		
		$this->db->trans_start();
		
		//query insert data		
		$this->db->flush_cache();
		$this->db->set('tgl_pendaftaran', date('Y-m-d'));
		$this->db->set('no_pendaftaran', $data['no_pendaftaran']);
        $this->db->set('id_psb', $data['id_psb']);
        $this->db->set('id_kelompok', $data['id_kelompok']);
        $this->db->set('tahun_masuk', $data['tahun_masuk']);
        $this->db->set('uang_sumbangan', $data['uang_sumbangan']);
        $this->db->set('nama', $data['nama']);
        $this->db->set('jk', $data['jk']);
        $this->db->set('tmp_lahir', $data['tmp_lahir']);
        $this->db->set('tgl_lahir', FormatDateToMysql($data['tgl_lahir']));
        $this->db->set('Agama', $data['Agama']);
        $this->db->set('kewarganegaraan', $data['kewarganegaraan']);
        $this->db->set('anakke', $data['anakke']);
        $this->db->set('jml_saudara', $data['jml_saudara']);
        $this->db->set('bahasa', $data['bahasa']);
        $this->db->set('foto', $data['foto']);
        $this->db->set('alamat', $data['alamat']);
        $this->db->set('tlp', $data['tlp']);
        $this->db->set('asal_sekolah', $data['asal_sekolah']);
        $this->db->set('gol_darah', $data['gol_darah']);
        $this->db->set('riwayat_penyakit', $data['riwayat_penyakit']);
        $this->db->set('nama_ayah', $data['nama_ayah']);
        $this->db->set('nama_ibu', $data['nama_ibu']);
        $this->db->set('status_ayah', $data['status_ayah']);
        $this->db->set('status_ibu', $data['status_ibu']);
        $this->db->set('alm_ayah', isset($data['alm_ayah'])?'1':'0');
        $this->db->set('alm_ibu', isset($data['alm_ibu'])?'1':'0');
        $this->db->set('tmp_lahir_ayah', $data['tmp_lahir_ayah']);
        $this->db->set('tmp_lahir_ibu', $data['tmp_lahir_ibu']);
        $this->db->set('tgl_lahir_ayah', $data['tgl_lahir_ayah']);
        $this->db->set('tgl_lahir_ibu', $data['tgl_lahir_ibu']);
        $this->db->set('pendidikan_ayah', $data['pendidikan_ayah']);
        $this->db->set('pendidikan_ibu', $data['pendidikan_ibu']);
        $this->db->set('pekerjaan_ayah', $data['pekerjaan_ayah']);
        $this->db->set('pekerjaan_ibu', $data['pekerjaan_ibu']);
        $this->db->set('penghasilan_ayah', $data['penghasilan_ayah']);
        $this->db->set('penghasilan_ibu', $data['penghasilan_ibu']);
        $this->db->set('nama_wali', $data['nama_wali']);
        $this->db->set('alamat_ortu', $data['alamat_ortu']);
        $this->db->set('tlp_ortu', $data['tlp_ortu']);
        $this->db->set('status', '1');
		
		$this->db->insert('tbl_sd_calonsiswa');
		
		for($i=0; $i<count($data['persyaratan']); $i++){
			$this->db->flush_cache();
			$this->db->set('id_calonsiswa', 	$inc);
			$this->db->set('id_persyaratan', 	$data['persyaratan'][$i]['id']);
			$this->db->set('persyaratan', 		$data['persyaratan'][$i]['persyaratan']);
			$this->db->set('ceklis', 			isset($data['chk_persyaratan'][$i])?'1':'0');
			
			$this->db->insert('tbl_sd_calonsiswa_persyaratan');
		}
		
		$this->db->trans_complete();
	    return $this->db->trans_status();		
		
	}
	
	function update($data){
		$this->db->trans_start();
		
		$this->db->flush_cache();
        $this->db->set('no_pendaftaran', $data['no_pendaftaran']);
        $this->db->set('id_psb', $data['id_psb']);
        $this->db->set('id_kelompok', $data['id_kelompok']);
        $this->db->set('tahun_masuk', $data['tahun_masuk']);
        $this->db->set('uang_sumbangan', $data['uang_sumbangan']);
        $this->db->set('nama', $data['nama']);
        $this->db->set('jk', $data['jk']);
        $this->db->set('tmp_lahir', $data['tmp_lahir']);
        $this->db->set('tgl_lahir', FormatDateToMysql($data['tgl_lahir']));
        $this->db->set('Agama', $data['Agama']);
        $this->db->set('kewarganegaraan', $data['kewarganegaraan']);
        $this->db->set('anakke', $data['anakke']);
        $this->db->set('jml_saudara', $data['jml_saudara']);
        $this->db->set('bahasa', $data['bahasa']);
        $this->db->set('foto', $data['foto']);
        $this->db->set('alamat', $data['alamat']);
        $this->db->set('tlp', $data['tlp']);
        $this->db->set('asal_sekolah', $data['asal_sekolah']);
        $this->db->set('gol_darah', $data['gol_darah']);
        $this->db->set('riwayat_penyakit', $data['riwayat_penyakit']);
        $this->db->set('nama_ayah', $data['nama_ayah']);
        $this->db->set('nama_ibu', $data['nama_ibu']);
        $this->db->set('status_ayah', $data['status_ayah']);
        $this->db->set('status_ibu', $data['status_ibu']);
        $this->db->set('alm_ayah', isset($data['alm_ayah'])?'1':'0');
        $this->db->set('alm_ibu', isset($data['alm_ibu'])?'1':'0');
        $this->db->set('tmp_lahir_ayah', $data['tmp_lahir_ayah']);
        $this->db->set('tmp_lahir_ibu', $data['tmp_lahir_ibu']);
        $this->db->set('tgl_lahir_ayah', $data['tgl_lahir_ayah']);
        $this->db->set('tgl_lahir_ibu', $data['tgl_lahir_ibu']);
        $this->db->set('pendidikan_ayah', $data['pendidikan_ayah']);
        $this->db->set('pendidikan_ibu', $data['pendidikan_ibu']);
        $this->db->set('pekerjaan_ayah', $data['pekerjaan_ayah']);
        $this->db->set('pekerjaan_ibu', $data['pekerjaan_ibu']);
        $this->db->set('penghasilan_ayah', $data['penghasilan_ayah']);
        $this->db->set('penghasilan_ibu', $data['penghasilan_ibu']);
        $this->db->set('nama_wali', $data['nama_wali']);
        $this->db->set('alamat_ortu', $data['alamat_ortu']);
        $this->db->set('tlp_ortu', $data['tlp_ortu']);
		
        $this->db->where('id', $data['id']);
        //$this->db->set('status', '1');
		
		$result = $this->db->update('tbl_sd_calonsiswa');
		
		for($i=0; $i<count($data['persyaratan']); $i++){
			$this->db->flush_cache();
			//$this->db->set('id_calonsiswa', 	$inc->row()->id);
			//$this->db->set('id_persyaratan', 	$data['persyaratan'][$i]['id']);
			//$this->db->set('persyaratan', 		$data['persyaratan'][$i]['persyaratan']);
			$this->db->set('ceklis', 			isset($data['chk_persyaratan'][$i])?'1':'0');
			$this->db->where('id', $data['persyaratan'][$i]['id']);
			
			$this->db->update('tbl_sd_calonsiswa_persyaratan');
		}
		
		$this->db->trans_complete();
	    return $this->db->trans_status();	
	}
	
	function delete($kode){
		$this->db->flush_cache();
		
		$this->db->where('id', $kode);
		$result = $this->db->delete('tbl_sd_calonsiswa');
	   
		//return
		if($result) {
				return TRUE;
		}else {
				return FALSE;
		}
	}

	function generate_persyaratan($aksi='add', $id_calonsiswa='0'){
		$out = '';
		//jika add maka ambil dari master
		$this->db->flush_cache();
		if($aksi == 'add'){
			$this->db->order_by('posisi', 'asc');
			$result = $this->db->get('tbl_sd_psb_persyaratan');
		}else{		//jika edit maka ambil dari detail calon siswa persyaratan
			$this->db->order_by('id', 'asc');
			$this->db->where('id_calonsiswa', $id_calonsiswa);
			$result = $this->db->get('tbl_sd_calonsiswa_persyaratan');
		}
		
		if($result->num_rows() > 0){
			$i=0;
			$out .= '<table style="margin-left:125px;width:390px;" border="0" cellpadding="2" cellspacing="1">';
			foreach($result->result() as $row){
				$out .= '<tr>';
				$out .=  '<td>';
				$out .=   '<input type="hidden" name="persyaratan['.$i.'][id]" value="'.$row->id.'"/>';
				$out .=   '<input type="hidden" name="persyaratan['.$i.'][persyaratan]" value="'.$row->persyaratan.'"/>';
				if($aksi == 'add'){
					$out .=   '<label><input type="checkbox" name="chk_persyaratan['.$i.']" checked="checked" size="2"/> '.$row->persyaratan.'</label>';
				}else{
					$out .=   '<label><input type="checkbox" name="chk_persyaratan['.$i.']" '.($row->ceklis=='1'?'checked="checked"':'').' size="2"/> '.$row->persyaratan.'</label>';
				}
				$out .=  '</td>';
				$out .= '</tr>';
				
				$i++;
			}
			$out .= '</table>';
			
			return $out;
		}else{
			return 'Data belum tersedia.<br>Masuk ke bagian setting untuk menambah data.';
		}
	}
	
}

?>